package com.taxsearch.controller.room;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.taxsearch.util.roomutil.PropertyUtil;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;


/**
 * @description: 
 *
 * @author: 杜一平
 *
 * @create: 2018年9月11日-上午11:20:57
 **/
public class DB {
	// 日志
	public static final Logger logger = LogManager
			.getLogger(PropertyUtil.class);
	private static Connection connection = null;
	private static PreparedStatement smt = null;
	private static ResultSet rs = null;
	
	//链接数据库
	public static Connection getConnection() {
		String USERNAMR = PropertyUtil.getProperty("jdbc_username").trim();
		String PASSWORD = PropertyUtil.getProperty("jdbc_password").trim();
		String DRVIER = PropertyUtil.getProperty("jdbc_driverClassName").trim();
		String URL = PropertyUtil.getProperty("jdbc_url").trim();
		try {
			Class.forName(DRVIER);
			connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
			logger.info("成功连接数据库");
		} catch (ClassNotFoundException e) {
			logger.info("ClassNotFound");
		} catch (SQLException e) {
			logger.info("连接分发库失败");
		}
		return connection;
	}
	/**
	 * 
	 * @Title: close
	 * @Description: 关闭连接
	 * @param:
	 * @return: void
	 * @throws
	 */
	public static void close(Connection connectionTemp) {
//		if (rs != null) {
//			try {
//				rs.close();
//			} catch (SQLException e) {
//				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
//			}
//		}
//		if (smt != null) {
//			try {
//				smt.close();
//			} catch (SQLException e) {
//				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
//			}
//		}
		if (connectionTemp != null) {
			try {
				connectionTemp.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接过程中失败，捕捉到SQLException", e);
			}
		}
	}
	
	/**
	 * 
	 * @Title: selectWuzheng
	 * @Description: 查询是否有无证权限
	 * @param: @param connection
	 * @param: @param hotelId
	 * @param: @return
	 * @param: @throws SQLException  
	 * @return: int   
	 * @throws
	 */
	public static int selectWuzheng(Connection connection,String hotelId) throws SQLException{
		
		String sql ="SELECT COUNT(1) FROM T_SOFTWARE  WHERE CODE = '"+hotelId+"' AND ENAME ='HotelAuth'";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<String>list=new ArrayList<>(5);
		int i=0;
		while(rs.next()){
			i=Integer.parseInt(rs.getString("COUNT(1)"));
		}
		return i;
		
	}


	/**
	 * 
	 * @Title: selectWuzhengShenpi
	 * @Description: 退房换房判断处于无证审批中
	 * @param: @param connection
	 * @param: @param ruzhuList
	 * @param: @return
	 * @param: @throws SQLException  
	 * @return: List<String>   
	 * @throws
	 */
	public static List<String> selectWuzhengShenpi(Connection connection ,List<String> ruzhuList) throws SQLException {	
		// 拼接入住id
		String id="";
		for(int j=0;j<ruzhuList.size();j++){
			if(j==0){
				id+="'"+ruzhuList.get(j)+"'";
			}else{
				id+=",'"+ruzhuList.get(j)+"'";
			}
		}
		
		String sql ="SELECT T.DJ_XINGMING FROM  CGUESTERSP T WHERE T.RECORDSNO in ("+id+") AND T.ENTERPRISE_ID ='0' ";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<String>list=new ArrayList<>(5);
		int i=0;
		while(rs.next()){
			i++;
			list.add(rs.getString("DJ_XINGMING"));
		}
		return list;
	}
	
	/**
	 * 
	 * @Title: selectIdType
	 * @Description: 获取证件类型
	 * @param: @param connection
	 * @param: @return
	 * @param: @throws SQLException  
	 * @return: List<HashMap<String,String>>   
	 * @throws
	 */
	public static List<HashMap<String, String>> selectIdType(Connection connection ) throws SQLException {	
		
		String sql ="SELECT DI_SELNO,DI_CVALUE FROM DICTIONARY WHERE DI_DFID='1246'";
		
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<HashMap<String, String>>list=new ArrayList<>(5);

		while(rs.next()){
			HashMap<String, String>map=new HashMap<>(2);
			map.put("DI_SELNO", rs.getString("DI_SELNO"));
			map.put("DI_CVALUE", rs.getString("DI_CVALUE"));
			list.add(map);
		}
		return list;
	}
	
	public static List<HashMap<String, String>> getGuoJi(Connection connection)throws SQLException{
		String sql ="select CODE,NAME from CODE_NATIONALITY ORDER BY CODE";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<HashMap<String, String>>list=new ArrayList<>(300);
		while(rs.next()){
			HashMap<String, String>map=new HashMap<>(2);
			map.put("DI_SELNO", rs.getString("CODE"));
			map.put("DI_CVALUE", rs.getString("NAME"));
			list.add(map);
		}
		return list;
	}
	public static List<HashMap<String, String>> getRuJingKouAn(Connection connection)throws SQLException{
		String sql ="select CODE,NAME from CODE_PORTOFENTRY group by code,name ORDER BY CODE ";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<HashMap<String, String>>list=new ArrayList<>(300);
		while(rs.next()){
			HashMap<String, String>map=new HashMap<>(2);
			map.put("DI_SELNO", rs.getString("CODE"));
			map.put("DI_CVALUE", rs.getString("NAME"));
			list.add(map);
		}
		return list;
	}
	
	public static List<HashMap<String, String>> getQianFaJiGuan(Connection connection)throws SQLException{
		String sql ="select CODE,NAME from CODE_VISAOFFICE ORDER BY CODE";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<HashMap<String, String>>list=new ArrayList<>(300);
		while(rs.next()){
			HashMap<String, String>map=new HashMap<>(2);
			map.put("DI_SELNO", rs.getString("CODE"));
			map.put("DI_CVALUE", rs.getString("NAME"));
			list.add(map);
		}
		return list;
	}
	
	public static List<HashMap<String, String>> getGuoJiaAndSheng(Connection connection)throws SQLException{
		String sql ="select * from CODE_AREA where NAME not like '%)' and (trim(translate(CODE,'0123456789',' ')) is not NULL or CODE like '%0000') order by code";
		smt=connection.prepareStatement(sql);
		rs = smt.executeQuery();
		List<HashMap<String, String>>list=new ArrayList<>(300);
		while(rs.next()){
			HashMap<String, String>map=new HashMap<>(2);
			map.put("DI_SELNO", rs.getString("CODE"));
			map.put("DI_CVALUE", rs.getString("NAME"));
			list.add(map);
		}
		return list;
	}
	
	public static void printList(List<HashMap<String, String>>list2){
		JSONArray jsonArray=new JSONArray();
		for(HashMap<String, String>map:list2){
			JSONObject jsonObject2=new JSONObject();
			jsonObject2.put("id", map.get("DI_SELNO"));
			jsonObject2.put("city", map.get("DI_CVALUE"));
			jsonArray.add(jsonObject2);
		}
		System.out.println(jsonArray);
	}
	

	
}
